/*
 * Copyright (c) 2023, LXMS (lxinet.cn).
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package cn.lxinet.lxms.generator.service;

import cn.lxinet.lxms.generator.Config;
import cn.lxinet.lxms.generator.domain.DatabaseColumn;
import cn.lxinet.lxms.generator.domain.Result;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.util.ClassUtils;

import java.io.BufferedReader;
import java.io.File;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.concurrent.*;

public class DataConnectionService {

    private static final Logger LOGGER = LoggerFactory.getLogger(DataConnectionService.class);

    /**
     * 获取指定表的字段信息
     *
     * @param tableName
     * @return
     */
    public Result getTableColumns(String tableName) {
        if (!tableName.matches("[a-zA-Z0-9_.]*")) {
            return Result.error("表名不合法");
        }
        Result result = executeQueryBySQL(tableName);
        List<Map<String, Object>> resultList = (List<Map<String, Object>>) result.getData();
        if (result.succeed()) {
            List<DatabaseColumn> resultColumn = columnMapToEntity(resultList);
            return Result.success(resultColumn);
        }
        return result;
    }

    /**
     * 字段信息映射到Column实体
     *
     * @param list
     * @return
     */
    private List<DatabaseColumn> columnMapToEntity(List<Map<String, Object>> list) {
        List<DatabaseColumn> resultList = new ArrayList<>();
        for (Map<String, Object> map : list) {
            DatabaseColumn column = new DatabaseColumn();
            column.setColumnName(map.get("COLUMN_NAME") == null ? null : map.get("COLUMN_NAME").toString());
            column.setColumnType(map.get("DATA_TYPE") == null ? null : map.get("DATA_TYPE").toString());
            column.setColumnComment(map.get("COLUMN_COMMENT") == null ? null : map.get("COLUMN_COMMENT").toString());
            column.setPrimary("PRI".equals(map.get("COLUMN_KEY")));
            resultList.add(column);
        }
        return resultList;
    }

    /**
     * 获取数据库连接
     *
     * @return
     */
    public Result getConnection() {
        String connUrl = "jdbc:mysql://" + Config.IP + ":" + Config.PORT + "/" + Config.DATABASE + "?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&useSSL=false&allowPublicKeyRetrieval=true";
        ExecutorService executor = Executors.newSingleThreadExecutor();
        FutureTask<Result> future = new FutureTask<>(() -> {
            try {
                Connection conn = DriverManager.getConnection(connUrl, Config.USERNAME, Config.PASSWORD);
                if (conn == null) {
                    return Result.error("连接失败");
                }
                return Result.success(conn);
            } catch (Exception e) {
                e.printStackTrace();
                LOGGER.error("connection error: {}", e.getMessage());
                return Result.error("连接失败", e.getMessage());
            } catch (Throwable t) {
                t.printStackTrace();
                LOGGER.error("connection error: {}", t.getMessage());
                return Result.error("连接失败，可能是指定的数据库版本不正确", t.getMessage());
            }
        });

        try {
            executor.execute(future);
            // 指定超时时间
            Result result = future.get(5000, TimeUnit.MILLISECONDS);
            return result;
        } catch (TimeoutException e) {
            return Result.error("数据库连接超时");
        } catch (Exception e) {
            return Result.error("系统异常");
        } finally {
            future.cancel(true);
            executor.shutdown();
        }
    }
    /**
     * 执行指定查询语句
     *
     * @return
     */
    public <T> Result executeQueryBySQL(String tableName) {
        StringBuffer sql = new StringBuffer();
        sql.append("select COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,NUMERIC_SCALE,COLUMN_KEY,COLUMN_COMMENT,IS_NULLABLE")
                .append(" from information_schema.columns")
                .append(" where table_name = '" + tableName + "' and table_schema = '" + Config.DATABASE + "'  order by ORDINAL_POSITION");
        Result getConnectionResult = getConnection();
        Connection conn = null;
        if (getConnectionResult.succeed()) {
            conn = (Connection) getConnectionResult.getData();
        }
        if (conn == null) {
            return Result.error("数据库连接失败！");
        }
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = conn.prepareStatement(sql.toString());
            resultSet = preparedStatement.executeQuery();
            List<Map<String, Object>> resultList = new ArrayList<>();
            ResultSetMetaData rsmd = resultSet.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (resultSet.next()) {
                Map<String, Object> map = new LinkedHashMap<>();
                for (int i = 1; i <= columnCount; i++) {
                    String columnName = rsmd.getColumnName(i);
                    switch (rsmd.getColumnTypeName(i).toUpperCase()) {
                        case "YEAR":
                            String yearStr = resultSet.getString(i);
                            if (yearStr != null) {
                                map.put(columnName, yearStr.substring(0, 4));
                            }
                            break;
                        case "DATETIME":
                            String str = resultSet.getString(i);
                            if (str != null) {
                                Timestamp dateTime = resultSet.getTimestamp(i);
                                if (dateTime != null) {
                                    String dateTimeStr = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(dateTime);
                                    map.put(columnName, dateTimeStr);
                                } else {
                                    map.put(columnName, null);
                                }
                            }
                            break;
                        case "TIMESTAMP":
                            String timestampStr = resultSet.getString(i);
                            map.put(columnName, timestampStr);
                            break;
                        case "DATE":
                            String dateStr = resultSet.getString(i);
                            if (dateStr != null) {
                                if (resultSet.getDate(i) != null) {
                                    dateStr = new SimpleDateFormat("yyyy - MM - dd").format(resultSet.getTimestamp(i));
                                }
                                map.put(columnName, dateStr);
                            }
                            break;
                        case "TIME":
                            Timestamp time = resultSet.getTimestamp(i);
                            if (time != null) {
                                String timeStr = new SimpleDateFormat("HH:mm:ss").format(time);
                                map.put(columnName, timeStr);
                            } else {
                                map.put(columnName, null);
                            }
                            break;
                        case "BIT":
                        case "VARBIT":
                            map.put(rsmd.getColumnName(i), Long.toBinaryString(resultSet.getLong(i)));
                            break;
                        case "BLOB":
                        case "BINARY":
                        case "VARBINARY":
                        case "TINYBLOB":
                        case "MEDIUMBLOB":
                        case "LONGBLOB":
                        case "BYTEA":
                            InputStream in = null;
                            try {
                                in = resultSet.getBinaryStream(i);
                                if (in == null) {
                                    break;
                                }
                                StringBuilder sb = new StringBuilder();
                                String line;
                                BufferedReader br = new BufferedReader(new InputStreamReader(in));
                                while ((line = br.readLine()) != null) {
                                    sb.append(line);
                                }
                                map.put(columnName, sb.toString());
                            } finally {
                                if (in != null) {
                                    in.close();
                                }
                            }
                            break;
                        case "CIRCLE":
                        case "PATH":
                        case "POINT":
                        case "POLYGON":
                        case "LINE":
                        case "LSEG":
                        case "BOX":
                            map.put(rsmd.getColumnName(i), resultSet.getString(i));
                            break;
                        default:
                            map.put(rsmd.getColumnName(i), resultSet.getObject(i));
                    }
                }
                resultList.add(map);
            }
            return Result.success(resultList);
        } catch (Exception e) {
            LOGGER.error("execute query sql exception: ", e);
            LOGGER.error("error sql : ", sql);
            return Result.error("执行SQL查询语句时出错 ", e.getMessage());
        } finally {
            disConnect(conn, preparedStatement, resultSet);
        }
    }


    /**
     * 关闭数据库连接
     *
     * @param conn
     * @param preparedStatement
     * @param resultSet
     */
    public void disConnect(Connection conn, PreparedStatement preparedStatement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (preparedStatement != null) {
                preparedStatement.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            LOGGER.error("disConnect exception: ", e);
        }
    }


    /**
     * 动态加载JDBC驱动
     *
     * @param driverPath 驱动类路径
     * @return
     * @throws Exception
     */
    public Driver getDriverLoaderByName(String driverPath) throws Exception {
        if (StringUtils.isBlank(driverPath)) {
            return null;
        }
        String jarPath = this.getDriverPath();
        String path = ClassUtils.getDefaultClassLoader().getResource(jarPath).getPath();
        File file = new File(path);
        if (!file.exists()) {
            LOGGER.error("{} 对应的驱动jar不存在.", driverPath);
            return null;
        }
        URLClassLoader loader = new URLClassLoader(new URL[]{file.toURI().toURL()}, null);
        loader.clearAssertionStatus();
        Driver driver = (Driver) Class.forName(driverPath, true, loader).newInstance();
        return driver;
    }

    private String getDriverPath() {
        if (Config.MYSQL_VERSION == 5){
            return "drivers/mysql-connector-java-5.1.34.jar";
        }else {
            return "drivers/mysql-connector-java-8.0.15.jar";
        }
    }
    private String getDriverName() {
        if (Config.MYSQL_VERSION == 5){
            return Config.DRIVER5;
        }else {
            return Config.DRIVER8;
        }
    }

}
